# coding=utf-8
from contextlib import closing

import MySQLdb

# db_host = 'localhost'
# db_user = 'root'
# db_passwd = 'root1234'
# db_db = 'scrapydb'
# db_port = 3306

class MySqlDriver(object):

    charset = 'utf8'

    def __init__(self, host='localhost', user='root', passwd='root1234', db='scrapydb', port=3306):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.db = db
        self.port = port

    def _get_connection(self):
        conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.db, port=self.port, charset=self.charset)
        return conn

    def execute_query_batch(self, query_string, batch_size):
        try:
            with closing(self._get_connection()) as conn:
                with closing(conn.cursor()) as cur:
                    cur = conn.cursor()
                    cur.execute(query_string)
                    query_result = cur.fetchmany(batch_size)
                    return query_result
        except MySQLdb.Error, e:
            print "Mysql query error %d: %s" % (e.args[0], e.args[1])

    def execute_query(self, query_string):
        try:
            with closing(self._get_connection()) as conn:
                with closing(conn.cursor()) as cur:
                    cur = conn.cursor()
                    cur.execute(query_string)
                    query_result = cur.fetchall()
                    return query_result
        except MySQLdb.Error, e:
            print "Mysql query error %d: %s" % (e.args[0], e.args[1])

    def insert(self, insert_string, item_tuple):
        try:
            with closing(self._get_connection()) as conn:
                with closing(conn.cursor()) as cur:
                    conn = self._get_connection()
                    cur = conn.cursor()
                    sql_insert = "insert into t_sinahealth_staged (title, content, spider, category, tag, url, referer, issue_date, source, createDtTm) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,current_timestamp)"
                    cur.execute(sql_insert, item_tuple)
                    conn.commit()
        except MySQLdb.Error, e:
            print "Mysql insert error %d: %s" % (e.args[0], e.args[1])
            conn.rollback()

